﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Xml;

using System.Data.OleDb;

namespace fpdToolkit
{
    public partial class FormEditDataSchema : Form
    {
        public FormEditDataSchema()
        {
            InitializeComponent();
            bDisableEvents = false;
            oldNick = "";
            oldFK = "";
        }

        bool bDisableEvents;

        private List<Stable> tables;

        Stable current_table;
        ScolumnDescriptor current_column;


        private void initColumn(ref ScolumnDescriptor column)
        {
            column.ColumnName = "<НЕ ЗАДАНО>";
            column.ColumnSize = 0;
            column.DataType = "";
            column.NumericPrecision = 0;
            column.NumericScale = 0;
            column.ProviderType = "";
            column.ext = new extended_elements();
            column.ext.key = false;
            column.ext.nick = "";
            column.ext.FK = "";
        }

        private string getColumnDescriptor(ScolumnDescriptor column)
        {
            StringBuilder str = new StringBuilder();
            string result = "";
            str.Append(column.ColumnName);
            str.Append(":<");
            str.Append(column.ext.nick);
            str.Append(">: ");
            if (string.Compare(column.ProviderType, "133") == 0)
            {
                str.Append("D");
            }
            if (string.Compare(column.ProviderType, "129") == 0)
            {
                str.Append("C(");
                str.Append(column.ColumnSize);
                str.Append(")");
            }
            if (string.Compare(column.ProviderType, "131") == 0)
            {
                str.Append("N(");
                str.Append(column.NumericPrecision);
                str.Append(",");
                str.Append(column.NumericScale);
                str.Append(")");
            }
            if (string.Compare(column.ProviderType, "11") == 0)
            {
                str.Append("L");
            }

            if (string.Compare(column.ProviderType, "201") == 0)
            {
                str.Append("M");
            }

            if (column.ext.key)
                str.Append(" KEY");
            if (!string.IsNullOrEmpty(column.ext.FK))
            {
                str.Append(" FK: <");
                str.Append(column.ext.FK);
                str.Append(">");
            }
            result = str.ToString();
            return result;
        }


        private void InitGui()
        {
            listBoxTable.Items.Clear();
            foreach (Stable table in tables)
            {
                listBoxTable.Items.Add(table.TableName);
                if (table.used)
                    listBoxTable.SetItemChecked(listBoxTable.Items.Count - 1, true);
            }
            listBoxTable.SelectedIndex = -1;
            listBoxFields.Items.Clear();
        }

        string xmlfile = "schema.xml";

        private void buttonLoad_Click(object sender, EventArgs e)
        {
            bool bOldElementOpen = false;
            bool bField = false;
            tables = null;
            string oldElement = "";

           

            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            // Set filter options and filter index.
            openFileDialog1.Filter = "Xml Files (.xml)|*.xml";
            openFileDialog1.FilterIndex = 1;

            openFileDialog1.Multiselect = true;

            // Call the ShowDialog method to show the dialog box.
            DialogResult ok = openFileDialog1.ShowDialog();

            // Process input if the user clicked OK.
            if (ok == System.Windows.Forms.DialogResult.OK)
            {
                // Open the selected file to read.
                xmlfile = openFileDialog1.FileName;
            }

            XmlTextReader reader = new XmlTextReader(xmlfile);
            current_column = new ScolumnDescriptor();
            current_column.ColumnName = "";
            current_column.ColumnSize = 0;
            current_column.DataType = "";
            current_column.NumericPrecision = 0;
            current_column.NumericScale = 0;
            current_column.ProviderType = "";
            current_column.ext = new extended_elements();
            current_column.ext.key = false;
            current_column.ext.nick = "";
            current_column.ext.FK = "";

            current_table = new Stable();
            current_table.TableName = "";
            current_table.columns = null;
            current_table.used = false;
            current_table.viewElement = "";
            current_table.sortElement = "";
            current_table.searchElement = "";
            int val;
            while (reader.Read())
            {
                // Обработка данных.
                //Console.WriteLine(reader.Name);
                switch (reader.NodeType)
                {
                    case XmlNodeType.Element: // Узел является элементом.
                        //                        Console.Write("<" + reader.Name);
                        //                        Console.WriteLine(">");
                        oldElement = reader.Name;
                        if (string.Compare(reader.Name, "FIELD") == 0)
                        {
                            bField = true;
                        }
                        bOldElementOpen = true;
                        break;
                    case XmlNodeType.Text: // Вывести текст в каждом элементе.
                        //Console.WriteLine(reader.Value);
                        val = 0;
                        if ((bOldElementOpen) && (string.Compare(oldElement, "NAME") == 0))
                        {
                            current_table = new Stable();
                            current_table.TableName = reader.Value;
                            current_table.columns = new List<ScolumnDescriptor>();
                            current_table.used = false;
                            current_table.viewElement = "";
                            current_table.sortElement = "";
                            current_table.searchElement = "";
                        }

                        if ((bOldElementOpen) && (string.Compare(oldElement, "USED") == 0))
                        {
                            val = 0;
                            Int32.TryParse(reader.Value, out val);
                            current_table.used = false;
                            if (1 == val)
                                current_table.used = true;
                        }

                        if ((bOldElementOpen) && (string.Compare(oldElement, "PRESENTATION") == 0))
                        {
                            current_table.viewElement = reader.Value;
                        }

                        if ((bOldElementOpen) && (string.Compare(oldElement, "SORT") == 0))
                        {
                            current_table.sortElement = reader.Value;
                        }

                        if ((bOldElementOpen) && (string.Compare(oldElement, "SEARCH") == 0))
                        {
                            current_table.searchElement = reader.Value;
                        }

                        if (bField)
                        {
                            if (string.Compare(oldElement, "ColumnName") == 0)
                            {
                                current_column.ColumnName = reader.Value;
                            }
                            if (string.Compare(oldElement, "ColumnSize") == 0)
                            {
                                Int32.TryParse(reader.Value, out val);
                                current_column.ColumnSize = val;
                            }
                            if (string.Compare(oldElement, "NumericPrecision") == 0)
                            {
                                Int32.TryParse(reader.Value, out val);
                                current_column.NumericPrecision = val;
                            }
                            if (string.Compare(oldElement, "NumericScale") == 0)
                            {
                                Int32.TryParse(reader.Value, out val);
                                current_column.NumericScale = val;
                            }
                            if (string.Compare(oldElement, "DataType") == 0)
                            {
                                current_column.DataType = reader.Value;

                            }
                            if (string.Compare(oldElement, "ProviderType") == 0)
                            {
                                current_column.ProviderType = reader.Value;
                            }

                            if (string.Compare(oldElement, "KEY") == 0)
                            {
                                Int32.TryParse(reader.Value, out val);
                                if (val == 1)
                                    current_column.ext.key = true;
                                else
                                    current_column.ext.key = false;
                            }
                            if (string.Compare(oldElement, "NICK") == 0)
                            {
                                current_column.ext.nick = reader.Value;
                            }
                            if (string.Compare(oldElement, "FK") == 0)
                            {
                                current_column.ext.FK = reader.Value;
                            }
                        }
                        break;
                    case XmlNodeType.EndElement: // Вывести конец элемента.
                        if (string.Compare(reader.Name, "FIELD") == 0)
                        {
                            bField = false;
                            current_table.columns.Add(current_column);
                            current_column = new ScolumnDescriptor();
                            initColumn(ref current_column);
                        }
                        if (string.Compare(reader.Name, "TABLE") == 0)
                        {
                            if (tables == null)
                            {
                                tables = new List<Stable>();
                            }
                            tables.Add(current_table);
                            current_table = new Stable();
                        }
                        bOldElementOpen = false;
                        //Console.Write("</" + reader.Name);
                        //Console.WriteLine(">");
                        break;
                }
            }
            reader.Close();
            InitGui();
        }

        private void FillFieldListbox()
        {
            listBoxFields.Items.Clear();
            if (listBoxTable.SelectedItem == null)
            {
                return;
            }
            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    textBoxPresentation.Text = table.viewElement;
                    textBoxSort.Text = table.sortElement;
                    textBoxSearch.Text = table.searchElement;
                    foreach (ScolumnDescriptor column in table.columns)
                    {
                        listBoxFields.Items.Add(getColumnDescriptor(column));
                    }
                    break;
                }
            }

        }

        private void listBoxTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DesignMode) return;
            FillFieldListbox();
        }

        void AddFieldDescription(XmlWriter textWriter, ScolumnDescriptor column)
        {
            textWriter.WriteStartElement("ColumnName");
            textWriter.WriteString(column.ColumnName);
            textWriter.WriteEndElement();

            textWriter.WriteStartElement("ColumnSize");
            textWriter.WriteString(column.ColumnSize.ToString());
            textWriter.WriteEndElement();

            textWriter.WriteStartElement("NumericPrecision");
            textWriter.WriteString(column.NumericPrecision.ToString());
            textWriter.WriteEndElement();

            textWriter.WriteStartElement("NumericScale");
            textWriter.WriteString(column.NumericScale.ToString());
            textWriter.WriteEndElement();

            textWriter.WriteStartElement("DataType");
            textWriter.WriteString(column.DataType);
            textWriter.WriteEndElement();

            textWriter.WriteStartElement("ProviderType");
            textWriter.WriteString(column.ProviderType);
            textWriter.WriteEndElement();

            // расширенные свойства
            textWriter.WriteStartElement("KEY");
            if (column.ext.key)
                textWriter.WriteString("1");
            else
                textWriter.WriteString("0");
            textWriter.WriteEndElement();

            if (!string.IsNullOrEmpty(column.ext.nick))
            {
                textWriter.WriteStartElement("NICK");
                textWriter.WriteString(column.ext.nick);
                textWriter.WriteEndElement();
            }

            if (!string.IsNullOrEmpty(column.ext.FK))
            {
                textWriter.WriteStartElement("FK");
                textWriter.WriteString(column.ext.FK);
                textWriter.WriteEndElement();
            }

        }

        void SaveDataToXML()
        {
            XmlWriterSettings settings = new XmlWriterSettings() { Indent = true };
            XmlWriter textWriter = XmlWriter.Create(xmlfile, settings);
            textWriter.WriteStartDocument();
            textWriter.WriteStartElement("FPD26");
            foreach (Stable table in tables)
            {
                textWriter.WriteStartElement("TABLE");
                textWriter.WriteStartElement("NAME");
                textWriter.WriteString(table.TableName);
                textWriter.WriteEndElement();

                textWriter.WriteStartElement("USED");
                if (table.used)
                    textWriter.WriteString("1");
                else
                    textWriter.WriteString("0");
                textWriter.WriteEndElement();

                textWriter.WriteStartElement("PRESENTATION");
                textWriter.WriteString(table.viewElement);
                textWriter.WriteEndElement();

                textWriter.WriteStartElement("SORT");
                textWriter.WriteString(table.sortElement);
                textWriter.WriteEndElement();

                textWriter.WriteStartElement("SEARCH");
                textWriter.WriteString(table.searchElement);
                textWriter.WriteEndElement();

                foreach (ScolumnDescriptor column in table.columns)
                {
                    textWriter.WriteStartElement("FIELD");
                    AddFieldDescription(textWriter, column);
                    textWriter.WriteEndElement();
                }
                textWriter.WriteEndElement();
            }
            textWriter.WriteEndElement();
            textWriter.WriteEndDocument();
            textWriter.Close();
        }

        private void buttonSave_Click(object sender, EventArgs e)
        {
            SaveDataToXML();
        }

        private void listBoxFields_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DesignMode) return;
            if (bDisableEvents) return;
            bDisableEvents = true;
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            if (listBoxFields.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            string selectedFieldName = GetSelectedName((string)listBoxFields.SelectedItem);

            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    foreach (ScolumnDescriptor column in table.columns)
                    {
                        if (string.Compare(column.ColumnName, selectedFieldName) == 0)
                        {
                            checkBoxKey.Checked = column.ext.key;
                            textBoxFK.Text = column.ext.FK;
                            textBoxNick.Text = column.ext.nick;
                            break;
                        }
                    }
                    break;
                }
            }
            bDisableEvents = false;
        }

        private string GetSelectedName(string item)
        {
            return item.Substring(0, item.IndexOf(':'));
        }

        private void checkBoxKey_CheckedChanged(object sender, EventArgs e)
        {
            int i, j;
            if (DesignMode) return;
            if (bDisableEvents) return;
            bDisableEvents = true;
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            if (listBoxFields.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            string selectedFieldName = GetSelectedName((string)listBoxFields.SelectedItem);
            for (i = 0; i < tables.Count; i++)
            {
                if (string.Compare(tables[i].TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    for (j = 0; j < tables[i].columns.Count; j++)
                    {
                        if (string.Compare(tables[i].columns[j].ColumnName, selectedFieldName) == 0)
                        {
                            tables[i].columns[j].ext.key = checkBoxKey.Checked;
                            break;
                        }
                    }
                    break;
                }
            }

            int curPos = listBoxFields.SelectedIndex;
            FillFieldListbox();
            listBoxFields.SelectedIndex = curPos;
            bDisableEvents = false;
        }

        private void listBoxTable_ItemCheck(object sender, ItemCheckEventArgs e)
        {
            if (listBoxTable.SelectedItem == null)
            {
                return;
            }
            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    table.used = !table.used;
                    break;
                }
            }
        }

        private string oldNick;
        private void textBoxNick_Enter(object sender, EventArgs e)
        {
            if (DesignMode) return;
            if (bDisableEvents) return;
            oldNick = textBoxNick.Text.Trim();
        }

        private void textBoxNick_Leave(object sender, EventArgs e)
        {
            int i, j;
            if (DesignMode) return;
            if (bDisableEvents) return;
            string current_nick = textBoxNick.Text.Trim();
            if (string.Compare(oldNick, current_nick) == 0) return;
            bDisableEvents = true;
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            if (listBoxFields.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            string selectedFieldName = GetSelectedName((string)listBoxFields.SelectedItem);
            for (i = 0; i < tables.Count; i++)
            {
                if (string.Compare(tables[i].TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    for (j = 0; j < tables[i].columns.Count; j++)
                    {
                        if (string.Compare(tables[i].columns[j].ColumnName, selectedFieldName) == 0)
                        {
                            tables[i].columns[j].ext.nick = current_nick;
                            break;
                        }
                    }
                    break;
                }
            }

            int curPos = listBoxFields.SelectedIndex;
            FillFieldListbox();
            listBoxFields.SelectedIndex = curPos;
            bDisableEvents = false;
        }

        private string oldFK;
        private void textBoxFK_Enter(object sender, EventArgs e)
        {
            if (bDisableEvents) return;
            if (DesignMode) return;
            oldFK = textBoxFK.Text.Trim();
        }

        private void textBoxFK_Leave(object sender, EventArgs e)
        {
            int i, j;
            if (DesignMode) return;
            if (bDisableEvents) return;
            string current_FK = textBoxFK.Text.Trim();
            if (string.Compare(oldFK, current_FK) == 0) return;
            bDisableEvents = true;
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            if (listBoxFields.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            string selectedFieldName = GetSelectedName((string)listBoxFields.SelectedItem);
            for (i = 0; i < tables.Count; i++)
            {
                if (string.Compare(tables[i].TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    for (j = 0; j < tables[i].columns.Count; j++)
                    {
                        if (string.Compare(tables[i].columns[j].ColumnName, selectedFieldName) == 0)
                        {
                            tables[i].columns[j].ext.FK = current_FK;
                            break;
                        }
                    }
                    break;
                }
            }

            int curPos = listBoxFields.SelectedIndex;
            FillFieldListbox();
            listBoxFields.SelectedIndex = curPos;
            bDisableEvents = false;
        }

        private void textBoxPresentation_Leave(object sender, EventArgs e)
        {
            if (bDisableEvents) return;
            if (DesignMode) return;
            bDisableEvents = true;
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    table.viewElement = textBoxPresentation.Text.Trim();
                    break;
                }
            }

            bDisableEvents = false;
        }

        private void buttonSQL_Click(object sender, EventArgs e)
        {
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    BuildSql(table);
                    break;
                }
            }
        }

        private List<string> PK(Stable table)
        {
            List<string> result = new List<string>();
            foreach (ScolumnDescriptor column in table.columns)
            {
                if (column.ext.key) result.Add(column.ColumnName);
            }
            return result;
        }

        private string GetParamExpression(string param, int cnt)
        {
            StringBuilder str = new StringBuilder();
            str.Append("cmd.Parameters.AddWithValue(\"param");
            str.Append(cnt);
            str.Append("\", field_");
            str.Append(param);
            str.Append(");");
            return str.ToString();
        }

        private string GetReadValueExpression(ScolumnDescriptor column, int cnt)
        {
            StringBuilder str = new StringBuilder();
            str.AppendFormat("field_{0}=({1})reader[\"{2}\"];", column.ColumnName, column.DataType, column.ColumnName);
            return str.ToString();
        }

        // построение команды выбора данных одного элемента по ключу
        void SelectItem(Stable table)
        {
            List<string> lst_params;
            bool bFirst;
            StringBuilder selectItem = new StringBuilder();
            lst_params = new List<string>();
            selectItem.Append("SELECT DISTINCT ");
            bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    selectItem.Append(", ");
                }
                selectItem.Append(table.TableName);
                selectItem.Append(".");
                selectItem.Append(column.ColumnName);
            }
            selectItem.Append(" FROM ");
            selectItem.Append(table.TableName);
            List<string> pk = PK(table);
            if (pk.Count > 0)
            {
                selectItem.Append(" WHERE (");
                bFirst = true;
                foreach (string pkval in pk)
                {
                    lst_params.Add(GetParamExpression(pkval, lst_params.Count));
                    if (bFirst)
                    {
                        bFirst = false;
                    }
                    else
                    {
                        selectItem.Append(" AND (");
                    }
                    selectItem.Append(table.TableName);
                    selectItem.Append(".");
                    selectItem.Append(pkval);
                    selectItem.Append(" = ?)");
                }
            }

        }


        // построение команды добавления новых данных
        void InsertNewData(Stable table)
        {
            List<string> lst_params;
            int cnt_prm;
            int i;
            bool bFirst;
            StringBuilder insertItem = new StringBuilder();
            lst_params = new List<string>();
            cnt_prm = 0;
            insertItem.Append("INSERT INTO ");
            insertItem.Append(table.TableName);
            insertItem.Append(" (");
            bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                lst_params.Add(GetParamExpression(column.ColumnName, lst_params.Count));
                cnt_prm++;
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    insertItem.Append(", ");
                }
                insertItem.Append(column.ColumnName);
            }
            insertItem.Append(") VALUES(");
            for (i = 0; i < cnt_prm; i++)
            {
                if (i != 0) insertItem.Append(", ");
                insertItem.Append("?");
            }
        }


        // построение команды обновления данных
        private void UpdateData(Stable table)
        {
            List<string> lst_params;
            int cnt_prm;
            bool bFirst;
            List<string> pk = PK(table);
            StringBuilder updateItem = new StringBuilder();
            lst_params = new List<string>();
            updateItem.Append("UPDATE ");
            updateItem.Append(table.TableName);
            updateItem.Append(" ");
            cnt_prm = 0;
            bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                if (column.ext.key) continue;
                lst_params.Add(GetParamExpression(column.ColumnName, lst_params.Count));
                cnt_prm++;
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    updateItem.Append(", ");
                }
                updateItem.Append("SET ");
                updateItem.Append(column.ColumnName);
                updateItem.Append(" = ?");
            }
            updateItem.Append(" WHERE (");
            bFirst = true;
            foreach (string pkval in pk)
            {
                lst_params.Add(GetParamExpression(pkval, lst_params.Count));
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    updateItem.Append(" AND (");
                }
                updateItem.Append(pkval);
                updateItem.Append(" = ?)");
            }
        }


        // построение команды удаления данных
        private void DeleteData(Stable table)
        {
            List<string> lst_params;
            bool bFirst;
            StringBuilder deleteItem = new StringBuilder();
            lst_params = new List<string>();
            List<string> pk = PK(table);
            if (pk.Count > 0)
            {
                deleteItem.Append("DELETE FROM ");
                deleteItem.Append(table.TableName);
                deleteItem.Append(" WHERE ");
                bFirst = true;
                foreach (string pkval in pk)
                {
                    lst_params.Add(GetParamExpression(pkval, lst_params.Count));
                    if (bFirst)
                    {
                        bFirst = false;
                    }
                    else
                    {
                        deleteItem.Append(" AND (");
                    }
                    deleteItem.Append(table.TableName);
                    deleteItem.Append(".");
                    deleteItem.Append(pkval);
                    deleteItem.Append(" = ?)");
                }
            }
        }

        private List<string> header_names = null;

        private string BuildListQuery(Stable table)
        {
            List<SJoinItem> join_list = new List<SJoinItem>();
            List<winform_item> items = new List<winform_item>();
            StringBuilder sql_query = new StringBuilder();
            StringBuilder sb_exp;
            header_names = new List<string>();
            sql_query.Append("SELECT DISTINCT ");
            bool bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                if (column.ext.key)
                {
                    // ключ текущего элемента
                    if (string.IsNullOrEmpty(column.ext.nick)) 
                    {
                        // ключи с незаданным именем для отображения не включаем в список отображаемых элементов
                        // в результат запроса они должны включаться
                        if (bFirst)
                        {
                            bFirst = false;
                        }
                        else
                        {
                            sql_query.Append(", ");
                        }
                        sql_query.Append(table.TableName);
                        sql_query.Append(".");
                        sql_query.Append(column.ColumnName);
                        continue;
                    }

                }
                if (!string.IsNullOrEmpty(column.ext.FK))
                {
                    // взаимосвязан с внешней таблицей - обрабатываем особым образом
                    sb_exp = new StringBuilder();
                    sb_exp.Append(table.TableName);
                    sb_exp.Append(".");
                    sb_exp.Append(column.ColumnName);
                    sb_exp.Append(" = ");
                    sb_exp.Append(column.ext.FK);
                    // получаем имя искомой таблицы
                    string fk_table = column.ext.FK.Substring(0, column.ext.FK.IndexOf('.'));
                    // ищем имя таблицы в списке join_list
                    bool bFound = false;
                    foreach (SJoinItem jitem in join_list)
                    {
                        if (string.Compare(jitem.TableName.ToUpper(), fk_table.ToUpper()) == 0)
                        {
                            jitem.expressions.Add(sb_exp.ToString());
                            bFound = true;
                            break;
                        }
                    }
                    if (!bFound)
                    {
                        SJoinItem new_jitem = new SJoinItem();
                        new_jitem.expressions = new List<string>();
                        new_jitem.expressions.Add(sb_exp.ToString());
                        new_jitem.TableName = fk_table;
                        join_list.Add(new_jitem);
                        // ищем запрос для отображения в табличной части
                        string vis_express = "";
                        foreach (Stable curtbl in tables)
                        {
                            if (string.Compare(curtbl.TableName.ToUpper(), fk_table.ToUpper()) == 0)
                            {
                                vis_express = curtbl.viewElement;
                                break;
                            }
                        }
                        // добавляем запрос для отображения в табличную часть
                        if (!string.IsNullOrEmpty(vis_express))
                        {
                            if (bFirst)
                            {
                                bFirst = false;
                            }
                            else
                            {
                                sql_query.Append(", ");
                            }
                            sql_query.Append("(");
                            sql_query.Append(vis_express);
                            sql_query.Append(") AS F");
                            sql_query.Append(fk_table);
                            // добавляем заголовок таблицы
                            header_names.Add(fk_table);
                        }

                    }
                    continue;
                }
                // обычное поле
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    sql_query.Append(", ");
                }

                // добавляем к запросу
                sql_query.Append(table.TableName);
                sql_query.Append(".");
                sql_query.Append(column.ColumnName);
                // добавляем к перечню отображаемых полей
                winform_item item = new winform_item();
                item.nick = column.ext.nick;
                item.name_item = "TextBox"+column.ColumnName;
                item.type_item = "TextBox";
                items.Add(item);

                // добавляем заголовок таблицы
                header_names.Add(column.ext.nick);

            }
            sql_query.Append(" FROM ");
            sql_query.Append(table.TableName);
            if (join_list.Count > 0)
            {
                bFirst = true;
                foreach (SJoinItem sjoin in join_list)
                {
                    sql_query.Append(" JOIN ");
                    sql_query.Append(sjoin.TableName);
                    sql_query.Append(" ON ");
                    foreach (string exp in sjoin.expressions)
                    {
                        if (bFirst)
                        {
                            bFirst = false;
                        }
                        else
                        {
                            sql_query.Append(" AND ");
                        }
                        sql_query.Append("(");
                        sql_query.Append(exp);
                        sql_query.Append(")");
                    }
                }
            }
            return sql_query.ToString();
        }

        private void BuildSql(Stable table)
        {
            List<string> lst_params;
            int cnt_prm;
            int i;
            StringBuilder selectAll = new StringBuilder();
            selectAll.Append("SELECT DISTINCT ");
            bool bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    selectAll.Append(", ");
                }
                selectAll.Append(table.TableName);
                selectAll.Append(".");
                selectAll.Append(column.ColumnName);
            }
            selectAll.Append(" FROM ");
            selectAll.Append(table.TableName);


            StringBuilder selectItem = new StringBuilder();
            lst_params = new List<string>();
            selectItem.Append("SELECT DISTINCT ");
            bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    selectItem.Append(", ");
                }
                selectItem.Append(table.TableName);
                selectItem.Append(".");
                selectItem.Append(column.ColumnName);
            }
            selectItem.Append(" FROM ");
            selectItem.Append(table.TableName);
            List<string> pk = PK(table);
            if (pk.Count > 0)
            {
                selectItem.Append(" WHERE (");
                bFirst = true;
                foreach (string pkval in pk)
                {
                    lst_params.Add(GetParamExpression(pkval, lst_params.Count));
                    if (bFirst)
                    {
                        bFirst = false;
                    }
                    else
                    {
                        selectItem.Append(" AND (");
                    }
                    selectItem.Append(table.TableName);
                    selectItem.Append(".");
                    selectItem.Append(pkval);
                    selectItem.Append(" = ?)");
                }
            }
            selectItem.AppendLine();
            foreach (string st in lst_params) { selectItem.Append(st); selectItem.AppendLine(); }
            selectItem.AppendLine();


            StringBuilder deleteItem = new StringBuilder();
            lst_params = new List<string>();
            deleteItem.Append("DELETE FROM ");
            deleteItem.Append(table.TableName);
            deleteItem.Append(" WHERE ");
            bFirst = true;
            foreach (string pkval in pk)
            {
                lst_params.Add(GetParamExpression(pkval, lst_params.Count));
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    deleteItem.Append(" AND (");
                }
                deleteItem.Append(table.TableName);
                deleteItem.Append(".");
                deleteItem.Append(pkval);
                deleteItem.Append(" = ?)");
            }
            deleteItem.AppendLine();
            foreach (string st in lst_params) { deleteItem.Append(st); deleteItem.AppendLine(); }
            deleteItem.AppendLine();

            StringBuilder insertItem = new StringBuilder();
            lst_params = new List<string>();
            cnt_prm = 0;  
            insertItem.Append("INSERT INTO ");
            insertItem.Append(table.TableName);
            insertItem.Append(" (");
            bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                lst_params.Add(GetParamExpression(column.ColumnName, lst_params.Count));
                cnt_prm++;
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    insertItem.Append(", ");
                }
                insertItem.Append(column.ColumnName);
            }
            insertItem.Append(") VALUES(");
            for (i = 0; i < cnt_prm; i++)
            {
                if (i != 0) insertItem.Append(", ");
                insertItem.Append("?");
            }
            insertItem.Append(")");
            insertItem.AppendLine();
            foreach (string st in lst_params) { insertItem.Append(st); insertItem.AppendLine(); }
            insertItem.AppendLine();

            StringBuilder updateItem = new StringBuilder();
            lst_params = new List<string>();
            updateItem.Append("UPDATE ");
            updateItem.Append(table.TableName);
            updateItem.Append(" ");
            cnt_prm = 0;
            bFirst = true;
            foreach (ScolumnDescriptor column in table.columns)
            {
                if (column.ext.key) continue;
                lst_params.Add(GetParamExpression(column.ColumnName, lst_params.Count));
                cnt_prm++;
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    updateItem.Append(", ");
                }
                updateItem.Append("SET ");
                updateItem.Append(column.ColumnName);
                updateItem.Append(" = ?");
            }
            updateItem.Append(" WHERE (");
            bFirst = true;
            foreach (string pkval in pk)
            {
                lst_params.Add(GetParamExpression(pkval, lst_params.Count));
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    updateItem.Append(" AND (");
                }
                updateItem.Append(pkval);
                updateItem.Append(" = ?)");
            }

            updateItem.Append(")");
            updateItem.AppendLine();
            foreach (string st in lst_params) { updateItem.Append(st); updateItem.AppendLine(); }
            updateItem.AppendLine();

            StringBuilder sb = new StringBuilder();
            sb.Append(BuildListQuery(table));
            sb.AppendLine();
            sb.Append(selectAll.ToString());
            sb.AppendLine();
            sb.Append(selectItem.ToString());
            sb.AppendLine();
            sb.Append(deleteItem.ToString());
            sb.AppendLine();
            sb.Append(insertItem.ToString());
            sb.AppendLine();
            sb.Append(updateItem.ToString());

            textBoxSQL.Text = sb.ToString();
            
        }


        /// <summary>
        /// Путь к базе данных
        /// </summary>
        public static string path_to_db { get { return Properties.Settings.Default.fpd_path; } set { Properties.Settings.Default.fpd_path = value; Properties.Settings.Default.Save(); } }
        //public static string path_to_db = @"C:\CKLAD";

        /// <summary>
        /// Строка подключения к БД Visual Foxpro
        /// </summary>
        /// <returns></returns>
        public static string connection_string()
        {
            return "Provider=VFPOLEDB.1;Data Source=" + path_to_db + ";DELETED=True";
        }

        private void buttonList_Click(object sender, EventArgs e)
        {
             if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    string sQuery = BuildListQuery(table);
                    string sconn = connection_string();
                    OleDbDataAdapter custDA = new OleDbDataAdapter();
                    OleDbConnection conn = new OleDbConnection(sconn);
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand(sQuery, conn);
                    custDA.SelectCommand = cmd;
                    DataTable dt = new System.Data.DataTable();
                    custDA.Fill(dt);
                    dataGridView1.AutoGenerateColumns = true;
                    bindingSource1.DataSource = dt;
                    for (int clm = 0; clm < dataGridView1.ColumnCount; clm++)
                    {
                        dataGridView1.Columns[clm].HeaderText = header_names[clm];
                    }
                    //dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
                    conn.Close();
                    break;
                }
            }
            
        }

        private void textBoxSort_Leave(object sender, EventArgs e)
        {
            if (bDisableEvents) return;
            if (DesignMode) return;
            bDisableEvents = true;
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    table.sortElement = textBoxSort.Text.Trim();
                    break;
                }
            }

            bDisableEvents = false;
        }

        private void textBoxSearch_Leave(object sender, EventArgs e)
        {
            if (bDisableEvents) return;
            if (DesignMode) return;
            bDisableEvents = true;
            if (listBoxTable.SelectedItem == null)
            {
                bDisableEvents = false;
                return;
            }
            foreach (Stable table in tables)
            {
                if (string.Compare(table.TableName, (string)listBoxTable.SelectedItem) == 0)
                {
                    table.searchElement = textBoxSearch.Text.Trim();
                    break;
                }
            }

            bDisableEvents = false;
        }
    }

    class winform_item
    {
        public string name_item; // наименование элемента управления
        public string type_item; // имя элемента управления
        public string nick;      // подпись рядом с элементом управления
    }

    class extended_elements
    {
        public bool key;
        public string nick;
        public string FK;
    }

    class ScolumnDescriptor
    {
        public string ColumnName;
        public int ColumnSize;
        public int NumericPrecision;
        public int NumericScale;
        public string DataType;
        public string ProviderType;
        public extended_elements ext;
    }

    class Stable
    {
        public string TableName;
        public List<ScolumnDescriptor> columns;
        public bool used;
        public string viewElement;
        public string sortElement;
        public string searchElement;
    }

    class SJoinItem
    {
        public string TableName; // имя присоединяемой таблицы
        public List<string> expressions; // выражения соединения двух и более таблиц
    }
}
